package com.yutel.system.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.yutel.system.dao.IExtPanelDao;
import com.yutel.system.repository.ExtComm;
import com.yutel.system.repository.ExtPanel;
import com.yutel.system.struts.core.Page;

@SuppressWarnings("unchecked")
public class ExtPanelDao extends JdbcDaoSupport implements IExtPanelDao {
	public List<ExtPanel> list(ExtPanel panel) {
		return listByPid(panel.getId());
	}
	public void add(ExtPanel panel) {
		panel.setPid(panel.getPid()<1?null:panel.getPid());
		panel.setId(next()+1);
		String sql="insert into ext_panel(id,pid,name,type,width,height,layout,region,table_id,table_name,sort) values(?,?,?,?,?,?,?,?,?,?,?)";
		getJdbcTemplate().update(sql,new Object[]{
				panel.getId(),panel.getPid(),panel.getName(),panel.getType(),panel.getWidth(),
				panel.getHeight(),panel.getLayout(),panel.getRegion(),panel.getTableId(),
				panel.getTableName(),panel.getSort()
		});
	}
	public void del(ExtPanel panel) {
		String sql="delete from ext_panel where id=?";
		getJdbcTemplate().update(sql,new Object[]{panel.getId()});
	}
	public void edit(ExtPanel panel) {
		panel.setPid(panel.getPid()<1?null:panel.getPid());
		String sql="update ext_panel set pid=?,name=?,type=?,width=?,height=?,layout=?,region=?,table_id=?,table_name=?,sort=? where id=?";
		getJdbcTemplate().update(sql,new Object[]{
				panel.getPid(),panel.getName(),panel.getType(),panel.getWidth(),
				panel.getHeight(),panel.getLayout(),panel.getRegion(),
				panel.getTableId(),panel.getTableName(),panel.getSort(),panel.getId()
		});
	}
	public List<ExtPanel> listByPid(int pid) {
		String sql = "select * from ext_panel where pid="+pid+" order by sort";
		if(pid<1) {
			sql="select * from ext_panel where pid is null order by sort";
		}
		return getJdbcTemplate().query(sql, new PanelRowMapper());
	}
	public ExtPanel view(int id) {
		String sql = "select * from ext_panel where id="+id;
		return (ExtPanel)getJdbcTemplate().queryForObject(sql, new PanelRowMapper());
	}
	public ExtPanel panel(ExtPanel panel) {
		panel.getComm().addAll(commByPage(panel.getId()));
		List<ExtPanel> list=listByPid(panel.getId());
		for(ExtPanel p:list) {
			ExtPanel subP=panel(p);
			panel.getSet().add(subP);
		}
		return panel;
	}
	
	public List<ExtComm> commQuery(ExtComm comm) {
		String sql = "select * from ext_comm where panel_id="+comm.getPanelId()
				+" and type='"+comm.getType()+"' order by sort";
		return getJdbcTemplate().query(sql, new CommRowMapper());
	}
	
	public List<ExtComm> commByPage(ExtPanel panel) {
		return commByPage(panel.getId());
	}
	public void addComm(ExtComm comm) {
		comm.setId(commNext()+1);
		String sql="insert into ext_comm(id,panel_id,type,name,col_id,col_name,col_type,col_null_able,hidden,target_id,sort) values(?,?,?,?,?,?,?,?,?,?,?)";
		getJdbcTemplate().update(sql,new Object[]{
				comm.getId(),comm.getPanelId(),comm.getType(),comm.getName(),
				comm.getColId(),comm.getColName(),comm.getColType(),comm.getColNullAble(),
				comm.getHidden(),comm.getTargetId(),comm.getSort()
		});
	}
	public void delComm(String commIds) {
		String sql="delete from ext_comm where id in("+commIds+")";
		getJdbcTemplate().update(sql);
	}
	public void editComm(ExtComm comm) {
		String sql="update ext_comm set panel_id=?,type=?,name=?,col_id=?,col_name=?,col_type=?,col_null_able=?,hidden=?,target_id=?,sort=? where id=?";
		getJdbcTemplate().update(sql,new Object[]{
				comm.getPanelId(),comm.getType(),comm.getName(),comm.getColId(),
				comm.getColName(),comm.getColType(),comm.getColNullAble(),
				comm.getHidden(),comm.getTargetId(),comm.getSort(),comm.getId()
		});
	}
	public List<ExtComm> commByPage(int id) {
		String sql = "select * from ext_comm where panel_id="+id+" order by sort";
		return getJdbcTemplate().query(sql, new CommRowMapper());
	}
	public ExtComm viewComm(ExtComm comm) {
		String sql = "select * from ext_comm where id="+comm.getId();
		return (ExtComm)getJdbcTemplate().queryForObject(sql, new CommRowMapper());
	}
	
	private int next() {
		String sql="select max(id) from ext_panel";
		return getJdbcTemplate().queryForInt(sql);
	}
	private int commNext() {
		String sql="select max(id) from ext_comm";
		return getJdbcTemplate().queryForInt(sql);
	}
	private class PanelRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			ExtPanel panel = new ExtPanel();
			panel.setId(rs.getInt("id"));
			panel.setPid(rs.getInt("pid"));
			panel.setName(rs.getString("name"));
			panel.setType(rs.getString("type"));
			panel.setWidth(rs.getInt("width"));
			panel.setHeight(rs.getInt("height"));
			panel.setLayout(rs.getString("layout"));
			panel.setRegion(rs.getString("region"));
			panel.setTableId(rs.getInt("table_id"));
			panel.setTableName(rs.getString("table_name"));
			panel.setSort(rs.getInt("sort"));
			return panel;
		}
	}
	private class CommRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			ExtComm comm = new ExtComm();
			comm.setId(rs.getInt("id"));
			comm.setPanelId(rs.getInt("panel_id"));
			comm.setType(rs.getString("type"));
			comm.setName(rs.getString("name"));
			comm.setColId(rs.getInt("col_id"));
			comm.setColName(rs.getString("col_name"));
			comm.setColType(rs.getString("col_type"));
			comm.setColNullAble(rs.getString("col_null_able"));
			comm.setHidden(rs.getString("hidden"));
			comm.setTargetId(rs.getInt("target_id"));
			comm.setSort(rs.getInt("sort"));
			return comm;
		}
	}
}
